import express from 'express';
import { randomUUID } from 'node:crypto';
import { McpServer } from '@modelcontextprotocol/sdk/server/mcp.js';
import { StreamableHTTPServerTransport } from '@modelcontextprotocol/sdk/server/streamableHttp.js';
import { isInitializeRequest } from '@modelcontextprotocol/sdk/types.js';
import { z } from 'zod';
import cors from 'cors';

// Import database connection and schema for MCP server
import { mcpDb as db } from '@/lib/mcp-db';
import { orders, orderItems, customers, products, orderActivities, inventory } from '@/lib/schema';
import { eq, desc, and, or, like, gte, lte, sql } from 'drizzle-orm';

// Create MCP server with real database access
export function createMcpServer() {
  const server = new McpServer({
    name: 'seller-management-api',
    version: '1.0.0',
    description: 'MCP server for seller management system API with real database access'
  });

  // Helper function to get status text
  function getStatusText(status: string): string {
    const statusMap: Record<string, string> = {
      'pending': '待支付',
      'paid': '已支付',
      'shipped': '已发货',
      'delivered': '已交付',
      'cancelled': '已取消'
    };
    return statusMap[status] || status;
  }

  // Helper function to get order details with real database query
  async function getOrderDetails(orderId: number) {
    try {
      const order = await db
        .select({
          id: orders.id,
          customerId: orders.customerId,
          customerName: customers.name,
          customerEmail: customers.email,
          customerPhone: customers.phone,
          customerAddress: customers.address,
          totalAmount: orders.totalAmount,
          status: orders.status,
          cancelReason: orders.cancelReason,
          createdAt: orders.createdAt,
          updatedAt: orders.updatedAt
        })
        .from(orders)
        .leftJoin(customers, eq(orders.customerId, customers.id))
        .where(eq(orders.id, orderId))
        .limit(1);

      if (order.length === 0) {
        return null;
      }

      const items = await db
        .select({
          id: orderItems.id,
          productId: orderItems.productId,
          productName: products.name,
          productPrice: orderItems.price,
          quantity: orderItems.quantity,
          subtotal: orderItems.subtotal
        })
        .from(orderItems)
        .leftJoin(products, eq(orderItems.productId, products.id))
        .where(eq(orderItems.orderId, orderId));

      const activities = await db
        .select({
          id: orderActivities.id,
          action: orderActivities.action,
          fromStatus: orderActivities.fromStatus,
          toStatus: orderActivities.toStatus,
          description: orderActivities.description,
          performedBy: orderActivities.performedBy,
          createdAt: orderActivities.createdAt
        })
        .from(orderActivities)
        .where(eq(orderActivities.orderId, orderId))
        .orderBy(desc(orderActivities.createdAt));

      return {
        ...order[0],
        items,
        activities
      };
    } catch (error) {
      console.error('Error fetching order details:', error);
      throw error;
    }
  }

  // Order Management Tools with Real Database Access

  server.registerTool(
    'get-order',
    {
      title: '获取订单详情',
      description: '根据订单ID获取订单详细信息，包括商品明细和活动记录',
      inputSchema: {
        orderId: z.number().describe('订单ID')
      }
    },
    async ({ orderId }) => {
      try {
        const order = await getOrderDetails(orderId);
        if (!order) {
          return {
            content: [{ type: 'text', text: `订单 ${orderId} 不存在` }],
            isError: true
          };
        }

        const orderText = `订单 #${order.id}
客户: ${order.customerName} (${order.customerEmail || '无邮箱'})
状态: ${getStatusText(order.status)}
总金额: ¥${order.totalAmount}
创建时间: ${new Date(order.createdAt * 1000).toLocaleString('zh-CN')}
更新时间: ${new Date(order.updatedAt * 1000).toLocaleString('zh-CN')}`;

        const itemsText = order.items.map(item => 
          `- ${item.productName}: ${item.quantity} × ¥${item.productPrice} = ¥${item.subtotal}`
        ).join('\n');

        const activitiesText = order.activities.map(activity => 
          `[${new Date(activity.createdAt * 1000).toLocaleString('zh-CN')}] ${activity.description}`
        ).join('\n');

        const result = `${orderText}\n\n商品明细:\n${itemsText}\n\n活动记录:\n${activitiesText}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `获取订单详情失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  server.registerTool(
    'list-orders',
    {
      title: '订单列表查询',
      description: '根据条件查询订单列表，支持状态、日期范围、客户名称等筛选',
      inputSchema: {
        status: z.string().optional().describe('订单状态: pending, paid, shipped, delivered, cancelled'),
        startDate: z.string().optional().describe('开始日期 (YYYY-MM-DD)'),
        endDate: z.string().optional().describe('结束日期 (YYYY-MM-DD)'),
        customerName: z.string().optional().describe('客户名称'),
        customerEmail: z.string().optional().describe('客户邮箱'),
        minAmount: z.number().optional().describe('最小金额'),
        maxAmount: z.number().optional().describe('最大金额'),
        limit: z.number().optional().default(20).describe('返回数量限制'),
        offset: z.number().optional().default(0).describe('偏移量')
      }
    },
    async ({ status, startDate, endDate, customerName, customerEmail, minAmount, maxAmount, limit, offset }) => {
      try {
        let query = db
          .select({
            id: orders.id,
            customerName: customers.name,
            customerEmail: customers.email,
            totalAmount: orders.totalAmount,
            status: orders.status,
            createdAt: orders.createdAt,
            updatedAt: orders.updatedAt
          })
          .from(orders)
          .leftJoin(customers, eq(orders.customerId, customers.id));

        // Apply filters
        const conditions = [];
        
        if (status) {
          conditions.push(eq(orders.status, status));
        }
        
        if (startDate) {
          const startTimestamp = Math.floor(new Date(startDate).getTime() / 1000);
          conditions.push(gte(orders.createdAt, startTimestamp));
        }
        
        if (endDate) {
          const endTimestamp = Math.floor(new Date(endDate).getTime() / 1000) + 86400; // Add 1 day
          conditions.push(lte(orders.createdAt, endTimestamp));
        }
        
        if (customerName) {
          conditions.push(like(customers.name, `%${customerName}%`));
        }
        
        if (customerEmail) {
          conditions.push(like(customers.email, `%${customerEmail}%`));
        }
        
        if (minAmount !== undefined) {
          conditions.push(gte(orders.totalAmount, minAmount));
        }
        
        if (maxAmount !== undefined) {
          conditions.push(lte(orders.totalAmount, maxAmount));
        }

        if (conditions.length > 0) {
          query = query.where(and(...conditions));
        }

        const orderList = await query
          .orderBy(desc(orders.createdAt))
          .limit(limit || 20)
          .offset(offset || 0);

        const totalCount = await db
          .select({ count: sql<number>`count(*)` })
          .from(orders)
          .leftJoin(customers, eq(orders.customerId, customers.id))
          .where(conditions.length > 0 ? and(...conditions) : undefined);

        const ordersText = orderList.map(order => 
          `#${order.id} | ${getStatusText(order.status)} | ¥${order.totalAmount} | ${order.customerName} | ${new Date(order.createdAt * 1000).toLocaleDateString('zh-CN')}`
        ).join('\n');

        const result = `找到 ${totalCount[0]?.count || 0} 个订单\n\n${ordersText || '未找到符合条件的订单'}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `查询订单列表失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  // Customer Management Tools with Real Database Access

  server.registerTool(
    'get-customer',
    {
      title: '获取客户信息',
      description: '根据客户ID获取客户详细信息',
      inputSchema: {
        customerId: z.number().describe('客户ID')
      }
    },
    async ({ customerId }) => {
      try {
        const customer = await db
          .select()
          .from(customers)
          .where(eq(customers.id, customerId))
          .limit(1);

        if (customer.length === 0) {
          return {
            content: [{ type: 'text', text: `客户 ${customerId} 不存在` }],
            isError: true
          };
        }

        const customerData = customer[0];
        const result = `客户 #${customerData.id}
姓名: ${customerData.name}
邮箱: ${customerData.email || '无'}
电话: ${customerData.phone || '无'}
地址: ${customerData.address || '无'}
创建时间: ${new Date(customerData.createdAt * 1000).toLocaleString('zh-CN')}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `获取客户信息失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  server.registerTool(
    'search-customers',
    {
      title: '搜索客户',
      description: '根据名称、邮箱或电话搜索客户',
      inputSchema: {
        searchTerm: z.string().describe('搜索关键词（名称、邮箱或电话）'),
        limit: z.number().optional().default(10).describe('返回数量限制')
      }
    },
    async ({ searchTerm, limit }) => {
      try {
        const customerList = await db
          .select()
          .from(customers)
          .where(or(
            like(customers.name, `%${searchTerm}%`),
            like(customers.email, `%${searchTerm}%`),
            like(customers.phone, `%${searchTerm}%`)
          ))
          .limit(limit || 10)
          .orderBy(desc(customers.createdAt));

        if (customerList.length === 0) {
          return {
            content: [{ type: 'text', text: `未找到匹配 "${searchTerm}" 的客户` }]
          };
        }

        const customersText = customerList.map(customer => 
          `#${customer.id} | ${customer.name} | ${customer.email || '无邮箱'} | ${customer.phone || '无电话'}`
        ).join('\n');

        const result = `找到 ${customerList.length} 个客户:\n\n${customersText}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `搜索客户失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  // Product Management Tools with Real Database Access

  server.registerTool(
    'get-product',
    {
      title: '获取产品信息',
      description: '根据产品ID获取产品详细信息',
      inputSchema: {
        productId: z.number().describe('产品ID')
      }
    },
    async ({ productId }) => {
      try {
        const product = await db
          .select({
            id: products.id,
            name: products.name,
            description: products.description,
            price: products.price,
            category: products.category,
            sku: products.sku,
            createdAt: products.createdAt
          })
          .from(products)
          .where(eq(products.id, productId))
          .limit(1);

        if (product.length === 0) {
          return {
            content: [{ type: 'text', text: `产品 ${productId} 不存在` }],
            isError: true
          };
        }

        const productData = product[0];
        
        // Get inventory info
        const [inventoryInfo] = await db
          .select({
            quantity: inventory.quantity,
            lowStockThreshold: inventory.lowStockThreshold
          })
          .from(inventory)
          .where(eq(inventory.productId, productId));

        let status = '正常';
        if (inventoryInfo && inventoryInfo.quantity === 0) {
          status = '缺货';
        } else if (inventoryInfo && inventoryInfo.quantity <= inventoryInfo.lowStockThreshold) {
          status = '低库存';
        }

        const result = `产品 #${productData.id}
名称: ${productData.name}
描述: ${productData.description || '无'}
价格: ¥${productData.price}
分类: ${productData.category || '未分类'}
SKU: ${productData.sku || '无'}
库存: ${inventoryInfo?.quantity || 0} 件
低库存阈值: ${inventoryInfo?.lowStockThreshold || 10} 件
状态: ${status}
创建时间: ${new Date(productData.createdAt * 1000).toLocaleString('zh-CN')}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `获取产品信息失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  server.registerTool(
    'list-products',
    {
      title: '产品列表查询',
      description: '查询产品列表，支持按分类、价格范围等筛选',
      inputSchema: {
        category: z.string().optional().describe('产品分类'),
        minPrice: z.number().optional().describe('最低价格'),
        maxPrice: z.number().optional().describe('最高价格'),
        inStock: z.boolean().optional().describe('仅显示有库存的产品'),
        limit: z.number().optional().default(20).describe('返回数量限制'),
        offset: z.number().optional().default(0).describe('偏移量')
      }
    },
    async ({ category, minPrice, maxPrice, inStock, limit, offset }) => {
      try {
        let query = db
          .select({
            id: products.id,
            name: products.name,
            price: products.price,
            category: products.category,
            sku: products.sku
          })
          .from(products);

        const conditions = [];
        
        if (category) {
          conditions.push(eq(products.category, category));
        }
        
        if (minPrice !== undefined) {
          conditions.push(gte(products.price, minPrice));
        }
        
        if (maxPrice !== undefined) {
          conditions.push(lte(products.price, maxPrice));
        }

        if (inStock) {
          query = query.leftJoin(inventory, eq(products.id, inventory.productId));
          conditions.push(gte(inventory.quantity, 1));
        }

        if (conditions.length > 0) {
          query = query.where(and(...conditions));
        }

        const productList = await query
          .orderBy(desc(products.createdAt))
          .limit(limit || 20)
          .offset(offset || 0);

        if (productList.length === 0) {
          return {
            content: [{ type: 'text', text: '未找到符合条件的商品' }]
          };
        }

        const productsText = productList.map(product => 
          `#${product.id} | ${product.name} | ¥${product.price} | ${product.category || '未分类'} | ${product.sku || '无SKU'}`
        ).join('\n');

        const result = `找到 ${productList.length} 个商品:\n\n${productsText}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `查询产品列表失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  // Inventory Management Tools with Real Database Access

  server.registerTool(
    'check-inventory',
    {
      title: '检查库存',
      description: '检查产品库存状态，包括低库存预警',
      inputSchema: {
        productId: z.number().describe('产品ID')
      }
    },
    async ({ productId }) => {
      try {
        const [inventoryInfo] = await db
          .select({
            quantity: inventory.quantity,
            lowStockThreshold: inventory.lowStockThreshold
          })
          .from(inventory)
          .where(eq(inventory.productId, productId));

        if (!inventoryInfo) {
          return {
            content: [{ type: 'text', text: `产品 ${productId} 的库存记录不存在` }]
          };
        }

        const product = await db
          .select({ name: products.name })
          .from(products)
          .where(eq(products.id, productId))
          .limit(1);

        const productName = product.length > 0 ? product[0].name : '未知产品';
        
        let status = '正常';
        if (inventoryInfo.quantity === 0) {
          status = '缺货';
        } else if (inventoryInfo.quantity <= inventoryInfo.lowStockThreshold) {
          status = '低库存';
        }

        const result = `产品: ${productName} (#${productId})
当前库存: ${inventoryInfo.quantity} 件
低库存阈值: ${inventoryInfo.lowStockThreshold} 件
状态: ${status}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `检查库存失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  server.registerTool(
    'list-low-stock',
    {
      title: '低库存产品列表',
      description: '列出所有低库存或缺货的产品',
      inputSchema: {
        limit: z.number().optional().default(20).describe('返回数量限制')
      }
    },
    async ({ limit }) => {
      try {
        const lowStockProducts = await db
          .select({
            productId: inventory.productId,
            productName: products.name,
            quantity: inventory.quantity,
            lowStockThreshold: inventory.lowStockThreshold
          })
          .from(inventory)
          .leftJoin(products, eq(inventory.productId, products.id))
          .where(or(
            eq(inventory.quantity, 0),
            lte(inventory.quantity, inventory.lowStockThreshold)
          ))
          .orderBy(inventory.quantity)
          .limit(limit || 20);

        if (lowStockProducts.length === 0) {
          return {
            content: [{ type: 'text', text: '没有低库存或缺货的产品' }]
          };
        }

        const productsText = lowStockProducts.map(product => 
          `#${product.productId} | ${product.productName} | ${product.quantity} 件 (阈值: ${product.lowStockThreshold}) | ${product.quantity === 0 ? '缺货' : '低库存'}`
        ).join('\n');

        const result = `低库存或缺货产品 (共 ${lowStockProducts.length} 个):\n\n${productsText}`;

        return {
          content: [{ type: 'text', text: result }]
        };
      } catch (error: any) {
        return {
          content: [{ type: 'text', text: `获取低库存列表失败: ${error.message}` }],
          isError: true
        };
      }
    }
  );

  // Dashboard Resources with Real Database Access

  server.registerResource(
    'dashboard-stats',
    'dashboard://stats',
    {
      title: '仪表盘统计数据',
      description: '系统仪表盘的关键统计数据',
      mimeType: 'application/json'
    },
    async (uri) => {
      try {
        const [
          totalOrders,
          totalRevenue,
          totalCustomers,
          totalProducts,
          pendingOrders,
          lowStockCount
        ] = await Promise.all([
          db.select({ count: sql<number>`count(*)` }).from(orders),
          db.select({ total: sql<number>`sum(total_amount)` }).from(orders).where(eq(orders.status, 'delivered')),
          db.select({ count: sql<number>`count(*)` }).from(customers),
          db.select({ count: sql<number>`count(*)` }).from(products),
          db.select({ count: sql<number>`count(*)` }).from(orders).where(eq(orders.status, 'pending')),
          db.select({ count: sql<number>`count(*)` }).from(inventory).where(
            or(
              eq(inventory.quantity, 0),
              lte(inventory.quantity, inventory.lowStockThreshold)
            )
          )
        ]);

        const stats = {
          totalOrders: totalOrders[0]?.count || 0,
          totalRevenue: totalRevenue[0]?.total || 0,
          totalCustomers: totalCustomers[0]?.count || 0,
          totalProducts: totalProducts[0]?.count || 0,
          pendingOrders: pendingOrders[0]?.count || 0,
          lowStockProducts: lowStockCount[0]?.count || 0,
          updatedAt: new Date().toISOString()
        };

        return {
          contents: [{
            uri: uri.href,
            text: JSON.stringify(stats, null, 2)
          }]
        };
      } catch (error: any) {
        return {
          contents: [{
            uri: uri.href,
            text: JSON.stringify({ error: `获取统计数据失败: ${error.message}` }, null, 2)
          }]
        };
      }
    }
  );

  return server;
}

// Create and configure Express app for Streamable HTTP transport
export function createApp() {
  const app = express();
  app.use(express.json());

  // Enable CORS for browser clients
  app.use(cors({
    origin: '*',
    exposedHeaders: ['Mcp-Session-Id'],
    allowedHeaders: ['Content-Type', 'mcp-session-id']
  }));

  // Map to store transports by session ID
  const transports: { [sessionId: string]: StreamableHTTPServerTransport } = {};

  // MCP POST endpoint
  app.post('/mcp', async (req, res) => {
    try {
      let transport: StreamableHTTPServerTransport;
      
      if (!req.body || !isInitializeRequest(req.body)) {
        // For non-initialization requests, require session ID
        const sessionId = req.headers['mcp-session-id'] as string | undefined;
        if (!sessionId || !transports[sessionId]) {
          res.status(400).json({
            jsonrpc: '2.0',
            error: {
              code: -32000,
              message: 'Bad Request: No valid session ID provided',
            },
            id: null,
          });
          return;
        }
        transport = transports[sessionId];
      } else {
        // New initialization request
        transport = new StreamableHTTPServerTransport({
          sessionIdGenerator: () => randomUUID(),
          onsessioninitialized: (sessionId) => {
            // Store the transport by session ID
            transports[sessionId] = transport;
          }
        });

        // Clean up transport when closed
        transport.onclose = () => {
          if (transport.sessionId) {
            delete transports[transport.sessionId];
          }
        };

        // Create and connect MCP server
        const server = createMcpServer();
        await server.connect(transport);

        await transport.handleRequest(req, res, req.body);
        return;
      }

      // Handle request with existing transport
      await transport.handleRequest(req, res, req.body);
    } catch (error) {
      console.error('Error handling MCP request:', error);
      if (!res.headersSent) {
        res.status(500).json({
          jsonrpc: '2.0',
          error: {
            code: -32603,
            message: 'Internal server error',
          },
          id: null,
        });
      }
    }
  });

  // Handle GET requests for SSE streams
  app.get('/mcp', async (req, res) => {
    const sessionId = req.headers['mcp-session-id'] as string | undefined;
    if (!sessionId || !transports[sessionId]) {
      res.status(400).send('Invalid or missing session ID');
      return;
    }

    const transport = transports[sessionId];
    await transport.handleRequest(req, res);
  });

  // Handle DELETE requests for session termination
  app.delete('/mcp', async (req, res) => {
    const sessionId = req.headers['mcp-session-id'] as string | undefined;
    if (!sessionId || !transports[sessionId]) {
      res.status(400).send('Invalid or missing session ID');
      return;
    }

    const transport = transports[sessionId];
    await transport.handleRequest(req, res);
  });

  // Health check endpoint
  app.get('/health', (req, res) => {
    res.json({ status: 'ok', service: 'seller-management-mcp', database: 'connected' });
  });

  return app;
}

// Export for standalone execution
export async function startHttpServer(port = 3001) {
  const app = createApp();
  
  return new Promise((resolve) => {
    const server = app.listen(port, () => {
      console.log(`MCP Streamable HTTP Server running on port ${port}`);
      console.log(`Health check: http://localhost:${port}/health`);
      console.log(`MCP endpoint: http://localhost:${port}/mcp`);
      console.log('Database: Connected to real database');
      resolve(server);
    });

    // Handle graceful shutdown
    process.on('SIGINT', async () => {
      console.log('Shutting down MCP server...');
      server.close(() => {
        console.log('Server shutdown complete');
        process.exit(0);
      });
    });
  });
}

// For standalone execution
if (import.meta.url === `file://${process.argv[1]}`) {
  const port = process.env.MCP_PORT ? parseInt(process.env.MCP_PORT) : 3001;
  startHttpServer(port).catch(console.error);
}